package com.example.tingshu.dao;

import com.example.tingshu.model.Album;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

@Repository
public class AlbumDao {
    private final DataSource dataSource;

    @Autowired
    public AlbumDao(DataSource dataSource) {
        this.dataSource = dataSource;
    }


    public List<Album> selectListByKeyword(String keyword) {
        keyword = "%" + keyword + "%";
        String sql = "SELECT aid,name,cover,count FROM album WHERE name LIKE ? ORDER BY aid DESC";
        List<Album> albumList = new ArrayList<>();

        try (Connection c = dataSource.getConnection()) {
            try (PreparedStatement s = c.prepareStatement(sql)){
                s.setString(1,keyword);
                try (ResultSet rs = s.executeQuery()){
                    while (rs.next()){
                        Album album = new Album();
                        album.aid = rs.getInt("aid");
                        album.name = rs.getString("name");
                        album.cover = rs.getString("cover");
                        album.count = rs.getInt("count");

                        albumList.add(album);
                    }
                }
            }
        }catch (SQLException exc){
            throw new RuntimeException(exc);
        }
        return albumList;
    }

    public List<Album> selectList() {
        String sql = "SELECT aid,name,cover,count FROM album ORDER BY aid DESC";
        List<Album> albumList = new ArrayList<>();

        try (Connection c = dataSource.getConnection()) {
            try (PreparedStatement s = c.prepareStatement(sql)){
                try (ResultSet rs = s.executeQuery()){
                    while (rs.next()){
                        Album album = new Album();
                        album.aid = rs.getInt("aid");
                        album.name = rs.getString("name");
                        album.cover = rs.getString("cover");
                        album.count = rs.getInt("count");

                        albumList.add(album);
                    }
                }
            }
        }catch (SQLException exc){
            throw new RuntimeException(exc);
        }
        return albumList;
    }

    public List<Album> selectListByUid(Integer uid) {
        String sql = "SELECT aid,name,cover,count FROM album WHERE uid = ? ORDER BY aid DESC";
        List<Album> albumList = new ArrayList<>();

        try (Connection c = dataSource.getConnection()) {
            try (PreparedStatement s = c.prepareStatement(sql)){
                s.setInt(1,uid);
                try (ResultSet rs = s.executeQuery()){
                    while (rs.next()){
                        Album album = new Album();
                        album.aid = rs.getInt("aid");
                        album.name = rs.getString("name");
                        album.cover = rs.getString("cover");
                        album.count = rs.getInt("count");

                        albumList.add(album);
                    }
                }
            }
        }catch (SQLException exc){
            throw new RuntimeException(exc);
        }
        return albumList;
    }

    public void insert(Integer uid, String name, String cover) {
        String sql = "INSERT INTO album (uid,name,cover) VALUES (?,?,?)";
        try (Connection c = dataSource.getConnection()){
            try (PreparedStatement s = c.prepareStatement(sql)){
                s.setInt(1,uid);
                s.setString(2,name);
                s.setString(3,cover);

                s.executeUpdate();
            }
        }catch (SQLException exc){
            throw new RuntimeException(exc);
        }
    }
}
